import smtplib
from email import encoders
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

import pymysql
import time
import random

def connectMysql():
    db=pymysql.connect(
        user = "root",
        password = "root",
        host = "localhost",
        database = "finance"
    )
    cursor=db.cursor()
    return db,cursor
def closeMysql(db,cursor):
    db.close()
    cursor.close()

# 得到加密后的密码
def getEncryptPassword(db, cursor, password):
    sql = "SELECT  MD5('" + password + "')"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.commit()
    for i in rows:
        for j in i:
            password = j
            return password

def generateRandomNumber(n):
    return ''.join([str(random.randint(0, 9)) for _ in range(n)])

def getRandomEmail():
    number = generateRandomNumber(10)
    email = f"{number}@qq.com"
    return email

def getUserIfNotExistAddUser(db, cursor, username, password, user):
    sql = "select * from user where username='{}' and password='{}'".format(username, password)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    if not result:
        sql = "select * from user where username='{}'".format(username)
        cursor.execute(sql)
        result = cursor.fetchall()
        db.commit()
        if result:
            sql = "delete from user where username='{}'".format(username)
            cursor.execute(sql)
            db.commit()
        sql = "insert into user(username,realname,password,IDcard,phone,email,paypwd,status,reputation) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        cursor.execute(sql, user)
        db.commit()
    time.sleep(1)

# 判断用户名是否存在 存在就删除用户
def ifExistUsernameDelUser(db, cursor, username):
    sql = "select * from user where username='{}'".format(username)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    if result:
        sql = "delete from user where username='{}'".format(username)
        cursor.execute(sql)
        db.commit()
    time.sleep(1)

#查询用户ID
def getUserByUsernameAndPassword(db, cursor, username,password):
    sql = "select * from user where username='{}' and password='{}'".format(username, password)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    return result

def checkBankCardBalance(db,cursor,userId,balance):
    sql = "select balance from bankcard where defaultl='1'  and userId='{}'".format(userId)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    if result!=balance:
        sql="update bankcard set balance='{}' where userId = '{}' and defaultl='1'".format(balance,userId)
        cursor.execute(sql)
        db.commit()

def checkUserDetail(db,cursor,user,userDetail):
    params=["username","realname","password","IDcard","phone","email","paypwd","status","reputation"]
    for i in range(len(user[0])):
        if i==0:
            continue
        if user[0][i]=="":
            sql="update user set {}='{}' where id='{}'".format(params[i-1],userDetail[i-1],user[0][0])
            cursor.execute(sql)
            db.commit()
    if user[0][7]!="666666":
        sql = "update user set paypwd='{}' where id='{}'".format("666666",user[0][0])
        cursor.execute(sql)
        db.commit()

def clearPassWord(db,cursor,userId):
    sql = "update user set paypwd=null where id='{}'".format(userId)
    cursor.execute(sql)
    db.commit()
    time.sleep(1)

def getBankIfNotExistAddBankCard(db, cursor, userId, bank):
    cardNum = bank[2]
    sql = "select * from bankcard where userId='{}'".format(userId, cardNum)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    if result:
        sql = "select * from bankcard where userId='{}' and cardNum='{}'".format(userId, cardNum)
        cursor.execute(sql)
        result2 = cursor.fetchall()
        db.commit()
        if result2:
            sql = "select * from bankcard where userId='{}' and cardNum='{}' and defaultl='1'".format(userId, cardNum)
            cursor.execute(sql)
            result = cursor.fetchall()
            if not result:
                sql = "update bankcard set defaultl='0'  where userId='{}'".format(userId, cardNum)
                cursor.execute(sql)
                db.commit()
                sql = "update bankcard set defaultl='1'  where userId='{}' and cardNum='{}'".format(userId, cardNum)
                cursor.execute(sql)
                db.commit()
        else:
            sql = "update bankcard set defaultl='0'  where userId='{}'".format(userId, cardNum)
            cursor.execute(sql)
            db.commit()
            sql = "insert into bankcard(cardBank,type,cardNum,userId,balance,defaultl) values(%s,%s,%s,%s,%s,%s)"
            cursor.execute(sql, bank)
            db.commit()
    else:
        sql = "insert into bankcard(cardBank,type,cardNum,userId,balance,defaultl) values(%s,%s,%s,%s,%s,%s)"
        cursor.execute(sql, bank)
        db.commit()

def deleteALLBankCardByUserId(db, cursor, userId):
    sql = "delete from bankcard where userId ='{}'".format(userId)
    cursor.execute(sql)
    db.commit()

def judgeIsNone(obj):
    if obj==None:
        return ""
    return obj

"""
    使用smtp发送邮件
    :param mail_sender: 发件人
    :param to_list: 收件人列表，用,间隔
    :param sub: 主题
    :param content: 内容
    :param attach_list:附件
    :param _subtype: 读取内容用的方式，不传html的话改成plain
    :return:
    """
def sendEmail(mail_sender, to_list,sub, content, attach_list = [], _subtype="html"):
    # 发件人邮箱地址
    from_email = mail_sender
    # 收件人邮箱地址
    to_emails = to_list
    # 邮件主题
    subject = sub
    # 邮件正文
    body = content
    # 附件文件路径
    attachment_paths = attach_list

    # 创建MIMEMultipart对象
    msg = MIMEMultipart()
    msg["From"] = from_email
    msg["To"] = ", ".join(to_emails)
    msg["Subject"] = subject

    # 添加邮件正文
    msg.attach(MIMEText(body, "plain"))

    # 添加附件
    for attachment_path in attachment_paths:
        print(attachment_path)
        with open(attachment_path, "rb") as attachment:
            part = MIMEBase("application", "octet-stream")
            part.set_payload(attachment.read())
            encoders.encode_base64(part)
            part.add_header(
                "Content-Disposition", f"attachment; filename= {attachment_path}"
            )
            msg.attach(part)

    # 连接到SMTP服务器并发送邮件
    server = smtplib.SMTP("smtp.qq.com", 25)
    server.starttls()
    server.login(from_email, "zxczcaunzcqnfgda")
    text = msg.as_string()
    for to_email in to_emails:  # 遍历收件人地址列表并发送邮件
        server.sendmail(from_email, to_email, text)
    server.quit()